﻿USE [msdb]
GO
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'DBA: Maintenance - Local' AND category_class=1)
	EXEC msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'DBA: Maintenance - Local'

DECLARE @jobId BINARY(16)
EXEC	msdb.dbo.sp_add_job @job_name=N'DBA: Load Server Current Information', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'DBA: Maintenance - Local', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT

EXEC	msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DBA: Discover New SQL Server Devices', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=3, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'PowerShell', 
		@command=N'Write-Output "Begin Discover SQL Servers"

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
$ErrorActionPreference = "continue"

$cn = new-object ''Microsoft.SqlServer.Management.Common.ServerConnection''
$cn.ConnectionString = "server=sp1-dba\prod;Trusted_Connection=true;"
$cn.Connect() | Out-Null
[Data.DataTable]$dtServers = [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources();
foreach ($row in $dtServers)
{
    $ServerName = $row.item("ServerName");
	
    if ($row.item("InstanceName") -and $row.item("InstanceName").Length -gt 0)
    {
         $ServerName += "\" + $row.item("InstanceName");
    }

    $msg = "Processing Server: " + $ServerName
    Write-Output $msg

    $cmd = "EXEC [$(DatabaseName)].[dbo].[spDBA_discover_server] ''" + $ServerName + "''";
    $cn.ExecuteWithResults($cmd) | Out-Null
}
$cn.Disconnect() | Out-Null

Write-Output "Complete Discover SQL Servers"
', 
		@database_name=N'master', 
		@flags=8

EXEC	msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DBA: Load Server Current Information', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=4, 
		@on_fail_action=3, 
		@on_fail_step_id=4, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'PowerShell', 
		@command=N'Write-Output "Begin Load Server Current Information"

$ErrorActionPreference = "continue"
$MSXServer = "sp1-dba\prod"

# Failsafe Server Information Query
$sFailsafeQuery = "
EXEC master.dbo.xp_instance_regread N''HKEY_LOCAL_MACHINE'', N''Software\Microsoft\MSSQLServer\MSSQLServer'', N''BackupDirectory'', @backup_dir OUTPUT

--- Get SQL Server service account information
IF CHARINDEX(''\'', CAST(SERVERPROPERTY(''ServerName'') AS varchar)) = 0
	SELECT	@service_name = ''SYSTEM\CurrentControlSet\Services\MSSQLSERVER''
ELSE 
	SELECT	@service_name = ''SYSTEM\CurrentControlSet\Services\MSSQL$'' + RIGHT(CAST(SERVERPROPERTY(''ServerName'') AS varchar), LEN(CAST(SERVERPROPERTY(''ServerName'') AS varchar)) - CHARINDEX(''\'',CAST(SERVERPROPERTY(''ServerName'') AS varchar),1))
EXEC master.dbo.xp_regread N''HKEY_LOCAL_MACHINE'', @service_name, N''ObjectName'', @sql_service OUTPUT

--- Get SQL Server Agent service account information
IF CHARINDEX(''\'', CAST(SERVERPROPERTY(''ServerName'') AS varchar)) = 0
	SELECT	@service_name = ''SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT''
ELSE 
	SELECT	@service_name = ''SYSTEM\CurrentControlSet\Services\SQLAGENT$'' + RIGHT(CAST(SERVERPROPERTY(''ServerName'') AS varchar), LEN(CAST(SERVERPROPERTY(''ServerName'') AS varchar)) - CHARINDEX(''\'',CAST(SERVERPROPERTY(''ServerName'') AS varchar),1))
EXEC master.dbo.xp_regread N''HKEY_LOCAL_MACHINE'', @service_name, N''ObjectName'', @agent_service OUTPUT

SELECT		SERVERPROPERTY(''ServerName'') server_name, 
	SERVERPROPERTY(''EngineEdition'') engine_edition, 
	(CASE	WHEN SERVERPROPERTY(''EditionID'') IS NOT NULL THEN SERVERPROPERTY(''EditionID'')
		WHEN SERVERPROPERTY(''EngineEdition'') = 1 THEN -1253826760
		WHEN SERVERPROPERTY(''EngineEdition'') = 2 THEN -1534726760
		WHEN SERVERPROPERTY(''EngineEdition'') = 3 THEN 1804890536
		ELSE 0
		END) edition_id, 
	SERVERPROPERTY(''ProductVersion'') product_version, 
	SERVERPROPERTY(''ProductLevel'') product_level,
	ISNULL(CAST(CAST(SUM(CASE WHEN sc.name = ''Jobs from MSX'' THEN 1 ELSE 0 END) AS bit) as int), 0) has_master_jobs,
	ISNULL(CAST(CAST(SUM(CASE WHEN sc.name = ''Jobs from MSX'' THEN 0 ELSE 1 END) AS bit) as int), 0) has_local_jobs,
	@backup_dir backup_directory,
	ISNULL(CAST(CAST(SERVERPROPERTY(''IsIntegratedSecurityOnly'') AS bit) as int), 0) integrated_only,
	@sql_service sql_service,
	@agent_service agent_service
FROM		msdb.dbo.sysjobs sj
JOIN		msdb.dbo.syscategories sc ON sj.category_id = sc.category_id
WHERE		sj.enabled = 1;"

# Open MSX Server Connection
$MSXConn = New-Object System.Data.SqlClient.SqlConnection
$MSXConn2 = New-Object System.Data.SqlClient.SqlConnection
$MSXConn.ConnectionString = "Persist Security Info=False;Integrated Security=true;Initial Catalog=$(DatabaseName);server=$MSXServer"
$MSXConn.Open()
$MSXConn2.ConnectionString = "Persist Security Info=False;Integrated Security=true;Initial Catalog=$(DatabaseName);server=$MSXServer"
$MSXConn2.Open()

# Instantiate the MSX Server Commands
$MSXCmd = $MSXConn.CreateCommand()
$MSXUpdateCmd = $MSXConn2.CreateCommand()
$MSXCmd.CommandTimeout = 0
$MSXUpdateCmd.CommandTimeout = 0

# Get the list of managed servers
$MSXCmd.CommandText = "EXEC dbo.spDBA_get_managed_server_list"
$servers = $MSXCmd.ExecuteReader()

# Loop through the servers
foreach ($server in $servers)
{
    # Only process servers that are not running on (local)
    if ( $server["srv_server_name"] -ne "(local)" -and $server["srv_server_name"] -ne "(local)\SQLEXPRESS" )
    {
       $msg = "Processing Server: " + $server["srv_server_name"]
       Write-Output $msg

        # Create the connection to the managed server
        $DestSrv = New-Object System.Data.SqlClient.SqlConnection
        $DestSrv.ConnectionString = "Persist Security Info=False;Integrated Security=true;Initial Catalog=master;server=" + $server["srv_server_name"]
        $DestSrv.Open() | Out-Null
        
        if ($DestSrv.State -eq [System.Data.ConnectionState]::Open)
        {
            $DestCmd = $DestSrv.CreateCommand()
            $DestCmd.CommandTimeout = 0

            # Get the server information from the managed server
            $sSQL = "DECLARE @backup_dir varchar(4000), @service_name nvarchar(255), @sql_service nvarchar(255), @agent_service nvarchar(255)
                     IF EXISTS (SELECT TOP 1 * FROM master.dbo.sysdatabases WHERE name = ''$(DatabaseName)'')
                      BEGIN 
                        IF EXISTS (SELECT TOP 1 * FROM [$(DatabaseName)].[dbo].[sysobjects] WHERE name = ''spMaintenanceGetServerInformation'')
                          BEGIN
                            EXEC (''USE [$(DatabaseName)]; EXEC dbo.spMaintenanceGetServerInformation;'') 
                          END
                        ELSE
                          BEGIN 
                            " + $sFailsafeQuery + " 
                          END
                      END 
                     ELSE 
                      BEGIN 
                      " + $sFailsafeQuery + " 
                      END;"
            $DestCmd.CommandText = $sSQL
            $srvInfo = $DestCmd.ExecuteReader()
            
            # Process the server information
            foreach ($srvInfoRow in $srvInfo)
            {
                $sSQL = "EXEC dbo.spDBA_update_server @server_name = ''" + $srvInfoRow["server_name"] + "'', "
                $sSQL = $sSQL + "@engine_edition = " + $srvInfoRow["engine_edition"] + ", "
                $sSQL = $sSQL + "@edition_id = " + $srvInfoRow["edition_id"] + ", "
                $sSQL = $sSQL + "@product_version = ''" + $srvInfoRow["product_version"] + "'', "
                $sSQL = $sSQL + "@product_level = ''" + $srvInfoRow["product_level"] + "'', "
                $sSQL = $sSQL + "@has_master_jobs = " + $srvInfoRow["has_master_jobs"] + ", "
                $sSQL = $sSQL + "@has_local_jobs = " + $srvInfoRow["has_local_jobs"] + ", "
                $sSQL = $sSQL + "@backup_directory = ''" + $srvInfoRow["backup_directory"] + "'', "
                $sSQL = $sSQL + "@integrated_only = " + $srvInfoRow["integrated_only"] + ", "
                $sSQL = $sSQL + "@sql_service = ''" + $srvInfoRow["sql_service"] + "'', "
                $sSQL = $sSQL + "@agent_service = ''" + $srvInfoRow["agent_service"] + "''"

                $MSXUpdateCmd.CommandText = $sSQL
                $MSXUpdateCmd.ExecuteNonQuery() | Out-Null
            }
            
            $srvInfo.Dispose()
            $DestCmd.Dispose()
            $DestSrv.Close()
        }
        else
        {
            # Failed to connect to the destination server
            $MSXUpdateCmd.CommandText = "EXEC dbo.spDBA_update_server_inerror  @server_name = ''" + $server["srv_server_name"] + "''"
            $MSXUpdateCmd.ExecuteNonQuery() | Out-Null
        }
    }
}

$MSXUpdateCmd.Dispose() | Out-Null
$MSXCmd.Dispose() | Out-Null
$MSXConn2.Close() | Out-Null
$MSXConn.Close() | Out-Null

Write-Output "Complete Load Server Current Information"', 
		@database_name=N'master', 
		@flags=8

EXEC	msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DBA: Disable Desktops', 
		@step_id=3, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=3, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'UPDATE		dbo.tblServer
SET		srv_is_active = 0,
		srv_is_disabled = 1
WHERE		srv_server_name LIKE ''A-%''
		AND srv_engine_edition IS NULL
		AND (
			ISNULL(srv_is_active, 0) <> 0
			OR ISNULL(srv_is_disabled, 0) <> 1
		)', 
		@database_name=N'$(DatabaseName)', 
		@flags=8

EXEC	msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DBA: Update Central Management Server List', 
		@step_id=4, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=3, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'EXEC dbo.spDBA_RebuildCentralServerList', 
		@database_name=N'$(DatabaseName)', 
		@flags=8

EXEC	msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DBA: Weekly Notify of Newly Discovered SQL Server Instances', 
		@step_id=5, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'DECLARE	@sSQL nvarchar(4000)

SELECT		@sSQL = ''
SELECT		srv_id,
		srv_date_discovered,
		srv_last_error,
		srv_last_update,
		srv_is_active,
		srv_is_disabled,
		srv_server_name,
		srv_engine_edition,
		srv_product_version,
		srv_product_level
FROM		[$(DatabaseName)].[dbo].[tblServer]
WHERE		srv_date_discovered >= DATEADD(day, -7, GETDATE())
ORDER BY	srv_is_active DESC,
		srv_is_disabled,
		srv_server_name
''

EXEC	(@sSQL)

IF @@ROWCOUNT > 0
  BEGIN
    IF DATEPART(Weekday, GETDATE()) = 2
      BEGIN
	--- Notify via e-mail of new instances found
	EXEC	msdb.dbo.sp_send_dbmail 
		@recipients = ''$(AdminEmailAccount)@$(AdminEmailDomainName)'', 
		@subject = ''Weekly: New SQL Server Instances Discovered'',
		@body = ''Please review where srv_is_active = 0 and srv_is_disabled = 0 and adjust properly (one of the two booleans should be 1)'',
		@query = @sSQL, 
		@attach_query_result_as_file = 1
      END
  END', 
		@database_name=N'$(DatabaseName)', 
		@flags=8

EXEC	msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

EXEC	msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'12:05 PM Daily', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20110707, 
		@active_end_date=99991231, 
		@active_start_time=120500, 
		@active_end_time=235959

EXEC	msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
GO
